
CREATE OR REPLACE FUNCTION holiday_days (boid integer, month INTEGER,year INTEGER) RETURNS INTEGER AS $$   
DECLARE 
	days INTEGER;
BEGIN
  SELECT COUNT(*) 
  INTO days 
  FROM holidays 
  WHERE branch_office_id = boid
  AND date_part('month', holiday_date ) = month 
  AND date_part('year', holiday_date ) = year
  AND extract(dow from holiday_date ) NOT IN (0, 6);
 
  IF days IS NULL THEN
	  SELECT 0 INTO days;
  END IF;
  RETURN days;
END;
$$ LANGUAGE plpgsql;

CREATE  OR REPLACE  FUNCTION off_hours (empid INTEGER, month INTEGER,year INTEGER) RETURNS REAL AS $$   

DECLARE offh REAL;
BEGIN
	SELECT SUM(hours)
  INTO offh
  FROM offs
  WHERE employee_id = empid
  AND date_part('month',off_date) = month 
  AND date_part('year', off_date ) = year;
  IF offh IS NULL THEN
	  SELECT 0.0 INTO offh;
  END IF;
RETURN offh;
END;
$$ LANGUAGE plpgsql;

-- Float to hours
CREATE OR REPLACE FUNCTION float2hour(decimal) RETURNS VARCHAR AS 'select
FLOOR(ROUND($1,2)) || ''h'' || FLOOR((ROUND($1,2)-FLOOR(ROUND($1,2)))*60)'
language sql;

create or replace function last_day(date) returns date as 'select
cast(date_trunc(''month'', $1) + ''1 month''::interval as date) - 1'
language sql;

CREATE OR REPLACE FUNCTION bo_business_days(boid INTEGER, month INTEGER,year INTEGER) RETURNS INTEGER AS $$ 
DECLARE month_start date;
        month_end date;
        hd INTEGER;
        days INTEGER;

BEGIN
	SELECT year ||'-'|| month || '-01' INTO month_start;
	SELECT last_day(month_start) INTO month_end;
  SELECT holiday_days(boid, month, year) INTO hd;
  SELECT business_days(month_start, month_end) - hd INTO days;
RETURN days;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION employee_hours (empid INTEGER,month INTEGER,year INTEGER)RETURNS REAL AS $$
DECLARE  h REAL;
         bo INTEGER;
         bdays INTEGER;
         hours_day REAL;
         eoff REAL;
BEGIN
  SELECT branch_office_id INTO bo FROM employees WHERE id = empid;
  SELECT hours/5 INTO hours_day FROM employees WHERE id = empid;
  SELECT bo_business_days(bo, month, year) INTO bdays;
  SELECT off_hours(empid, month, year) INTO eoff;
  SELECT bdays * hours_day - eoff INTO h;
RETURN h;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION business_days (d1 DATE, d2 DATE)    RETURNS INTEGER AS $$                  

  DECLARE dow1 INTEGER; dow2 INTEGER; days INTEGER;
BEGIN

  SELECT EXTRACT(DOW FROM d1) INTO dow1;
  SELECT EXTRACT(DOW FROM d2) INTO dow2;
  days := FLOOR( (d2-d1)/7 ) * 5 +                                         
    CASE
      WHEN dow1=1 AND dow2=7 THEN 5 
      WHEN dow1 IN (7,1) AND dow2 IN (7,1) THEN 0
      WHEN dow1=dow2 THEN 1 
      WHEN dow1 IN (7,1) AND dow2 NOT IN (7,1) THEN dow2-1 
      WHEN dow1 NOT IN (7,1) AND dow2 IN (7,1) THEN 7-dow1
      WHEN dow1<=dow2 THEN dow2-dow1+1
      WHEN dow1>dow2 THEN 5-(dow1-dow2-1)
      ELSE 0
    END;
  RETURN days;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION worked_hours (empid INTEGER,month INTEGER,year INTEGER)  RETURNS REAL AS $$    
DECLARE 
      h REAL;
      in1 REAL;
      in2 REAL;
      
      out2 REAL;
BEGIN
     
     SELECT SUM(EXTRACT(EPOCH FROM time) - EXTRACT(EPOCH FROM cast(to_char(time, 'YYYY-MM-DD')||' 00:00:00' as TIMESTAMP))) INTO in2 FROM hours where employee_id = empid AND in_or_out = 'in' AND date_part('month', time) = month AND date_part('year', time) = year;
          SELECT SUM(EXTRACT(EPOCH FROM time) - EXTRACT(EPOCH FROM cast(to_char(time, 'YYYY-MM-DD')||' 00:00:00' as TIMESTAMP))) INTO out2 FROM hours where employee_id = empid AND in_or_out = 'out' AND date_part('month', time) = month AND date_part('year', time) = year;
     SELECT (out2 - in2)/3600 INTO h;
     RETURN h;
END;

$$ LANGUAGE plpgsql;

